<html>
<body>

<form action = "innerjointest.php" method = "post">
User: <input type = "text" name = "user"/>
<input type = "submit" />
</form>

<?php

//Creates connection, confirms connection. Be sure to fill in the correct user name and password.

$con = mysql_connect("localhost" , "root" , "password");

if (!$con) die ("Could not connect" . mysql_error());
else print "ok";

print "<br>";

//Retrieves user_id from form and prints how many movies they like. Much of this is useless, except for mysql_select_db.

$user = $_POST["user"];

mysql_select_db("db" , $con);

$query = mysql_query("select count(*) from data where user_id = " . $user);

$query = mysql_fetch_array($query);

print $query[0];

print "<br>";

//This is the money. It returns a list of users similar to $user. Descending order. Note the first user_id is likely to be $user themself.

$query = mysql_query("select a.user_id as user, count(a.item_id) as b from db.data a inner join db.data b on a.item_id = b.item_id where b.user_id = " . $user . "  and a.rating = b.rating group by a.user_id order by b desc");

//Prints results. I need to add on some already built sql syntax to return movies instead of user_id's.


$user_array = mysql_fetch_array($query);

print $user_array['user'];

$string1 = "user_id = " . $user_array['user'];


$i = 0;

while($i <= 30)
	{
	$user_array = mysql_fetch_array($query);
	$string1 = $string1 . " or user_id = " . $user_array['user'];
	$i++;
	}

mysql_query("drop table if exists temp");

$string = "create table temp select item_id as item, count(*) as count, avg(rating) as rating from data where " . $string1 . " group by item_id order by count(*) desc limit 0,200";

print $string;


mysql_query($string);

$string2 = "select item.movie_title as title, temp.item as id, temp.count as counts, temp.rating as ratings from temp join item on item.movie_id = temp.item left join data on (temp.item = data.item_id and user_id = " . $user . ") where data.item_id is null group by temp.item order by temp.count*temp.rating desc limit 0,30";



print "<br>";
print "<br>";

print $string2;

$item = mysql_query($string2);

print "<br>";

echo "<table border='1'>
<tr>
<th>Title</th>
<th>Predicted Rating</th>
<th>I.D.</th>
<th>Confidence</th>
</tr>";

while ($item_array = mysql_fetch_array($item))
	{
	echo "<tr>";
	echo "<td>" . $item_array['title'] . "</td>";
	echo "<td>" . $item_array['ratings'] . "</td>";
	echo "<td>" . $item_array['id'] . "</td>";
	echo "<td>" . $item_array['counts'] . "</td>";
	}
mysql_query("drop table temp");

mysql_close($con);

?>

</body>
</html>
